IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_MediaContentAddMany]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_MediaContentAddMany]
GO
CREATE PROCEDURE [dbo].[cms_sp_MediaContentAddMany] 
(
    @SiteId          INT = NULL
   ,@SiteGroupId     INT = NULL
   ,@MediaId         INT
   ,@ContentXml      XML
   ,@CreatedBy       INT
   ,@DateCreated     DATETIME
)
AS
	-- @ContentXml must be like this:
	--'<xml>
	--	<MediaContents>
	--		<MediaContent>
	--			<MediaContentName>First</MediaContentName>
	--			<Description>Empty</Description>
	--			<MetaCode>CodeFirst</MetaCode>
	--			<MediaFiles>
	--				<MediaFile>
	--					<MediaFileId>1</MediaFileId>
	--					<IsPrimary>1</IsPrimary>
	--				</MediaFile>
	--				<MediaFile>
	--					<MediaFileId>2</MediaFileId>
	--					<IsPrimary>0</IsPrimary>
	--				</MediaFile>
	--			</MediaFiles>
	--			<MediaTags>
	--				<MediaTag>
	--					<MediaTagId>1</MediaTagId>
	--				</MediaTag>
	--				<MediaTag>
	--					<MediaTagId>2</MediaTagId>
	--				</MediaTag>
	--			</MediaTags>
	--		</MediaContent>
	--		<MediaContent>
	--			<MediaContentName>Second</MediaContentName>
	--			<Description>Empty</Description>
	--			<MetaCode>CodeSecond</MetaCode>
	--			<MediaFiles>
	--				<MediaFile>
	--					<MediaFileId>2</MediaFileId>
	--					<IsPrimary>1</IsPrimary>
	--				</MediaFile>
	--			</MediaFiles>
	--			<MediaTags>
	--			</MediaTags>
	--		</MediaContent>
	--	</MediaContents>
	--</xml>' 
	
	SET ARITHABORT ON 
	
	DECLARE @MediaContentId INT 
	DECLARE @MediaContentName NVARCHAR(250) 
	DECLARE @MetaCode NVARCHAR(150) 
	DECLARE @Description NVARCHAR(500) 
	DECLARE @MediaFileList XML 
	DECLARE @MediaTagIdList XML 
	DECLARE @BlockFromSearchEngines BIT 
	
	DECLARE media_content_cursor CURSOR  
	FOR
	    SELECT doc.col.value('(./MediaContentName)[1]' ,'nvarchar(250)')
	          ,doc.col.value('(./Description)[1]' ,'nvarchar(500)')
	          ,doc.col.value('(./MetaCode)[1]' ,'nvarchar(150)')
	          ,doc.col.query('(./MediaFiles/MediaFile/.)')
	          ,doc.col.query('(./MediaTags/MediaTag/MediaTagId/.)')
	          ,doc.col.value('(./BlockFromSearchEngines)[1]' ,'bit')
	    FROM   @ContentXml.nodes('/MediaContents/MediaContent') doc(col) 
	
	OPEN media_content_cursor; 
	
	FETCH NEXT FROM media_content_cursor 
	INTO @MediaContentName, @Description, @MetaCode, @MediaFileList, @MediaTagIdList, 
	@BlockFromSearchEngines 
	
	WHILE @@fetch_status = 0
	BEGIN
	    INSERT INTO dbo.MediaContents
	      (
	        SiteId
	       ,SiteGroupId
	       ,MediaId
	       ,MediaContentName
	       ,DESCRIPTION
	       ,MetaCode
	       ,CreatedBy
	       ,DateCreated
	       ,BlockFromSearchEngines
	      )
	    VALUES
	      (
	        @SiteId
	       ,@SiteGroupId
	       ,@MediaId
	       ,@MediaContentName
	       ,@Description
	       ,@MetaCode
	       ,@CreatedBy
	       ,@DateCreated
	       ,@BlockFromSearchEngines
	      ) 
	    
	    SET @MediaContentId = SCOPE_IDENTITY() 
	    
	    INSERT INTO dbo.MediaContentFileLinks
	      (
	        MediaContentId
	       ,MediaFileId
	       ,IsPrimary
	      )
	    SELECT @MediaContentId
	          ,childDoc.col.value('(./MediaFileId)[1]' ,'int')
	          ,childDoc.col.value('(./IsPrimary)[1]' ,'bit')
	    FROM   @MediaFileList.nodes('/MediaFile') childDoc(col) 
	    
	    INSERT INTO dbo.MediaContentTagLinks
	      (
	        MediaContentId
	       ,MediaTagId
	      )
	    SELECT @MediaContentId
	          ,childDoc.col.value('(.)[1]' ,'int')
	    FROM   @MediaTagIdList.nodes('/MediaTagId') childDoc(col) 
	    
	    FETCH NEXT FROM media_content_cursor 
	    INTO @MediaContentName, @Description, @MetaCode, @MediaFileList, @MediaTagIdList, 
	    @BlockFromSearchEngines
	END 
	
	CLOSE media_content_cursor; 
	DEALLOCATE media_content_cursor;
GO